create  table jms_dm.dm_terminal_city_to_city_sign_dt 
(
end_city_id varchar(100) COMMENT '末端网点所属城市id' ,
pick_city_id   varchar(100) COMMENT '寄件城市id' ,
end_provider_id varchar(100) COMMENT '末端网点所属省份id' ,
end_agent_code varchar(100) COMMENT '末端网点所属代理区编码' ,
pick_agent_code  varchar(100) COMMENT '寄件代理区code' ,
end_agent_name varchar(100) COMMENT '末端网点所属代理区名称' ,
end_provider_name varchar(100) COMMENT '末端网点所属省份名称' ,
end_city_name varchar(100) COMMENT '末端网点所属城市名称' ,
pick_agent_name  varchar(100) COMMENT '寄件代理区' ,
pick_provider_id   varchar(100) COMMENT '寄件省份id' ,
pick_provider_name   varchar(100) COMMENT '寄件省' ,
pick_city_name    varchar(100) COMMENT '寄件城市' ,
need_sign_count bigint COMMENT '应签收汇总' ,
aging_sign_in_time_network bigint COMMENT '时效签收准点网点签收' ,
aging_sign_in_time_terminal_pdd bigint COMMENT '时效签收准点终端入库(桃花岛认证)' ,
aging_sign_in_time_terminal_other bigint COMMENT '时效签收准点终端入库(其他)' ,
aging_sign_in_time_proxy bigint COMMENT '时效签收准点代理点收入' ,
aging_sign_in_time_transfer bigint COMMENT '时效签收准点转邮' ,
aging_sign_in_time_count bigint COMMENT '时效签收准点汇总' ,
22_aging_sign_over_time_network bigint COMMENT '时效签收延误网点签收' ,
22_aging_sign_over_time_terminal_pdd bigint COMMENT '时效签收延误终端入库(桃花岛认证)' ,
22_aging_sign_over_time_terminal_other bigint COMMENT '时效签收延误终端入库(其他)' ,
22_aging_sign_over_time_proxy bigint COMMENT '时效签收延误代理点收入' ,
22_aging_sign_over_time_difficult bigint COMMENT '时效签收延误问题件' ,
22_aging_sign_over_time_other bigint COMMENT '时效签收延误其他' ,
22_aging_sign_over_time_transfer bigint COMMENT '时效签收延误转邮' ,
22_aging_sign_over_time_sign_count bigint COMMENT '时效签收延误已签收汇总' ,
22_aging_sign_over_time_nosign_count bigint COMMENT '时效签收延误未签收汇总' ,
24_aging_sign_over_time_network bigint COMMENT '时效签收延误网点签收' ,
24_aging_sign_over_time_terminal_pdd bigint COMMENT '时效签收延误终端入库(桃花岛认证)' ,
24_aging_sign_over_time_terminal_other bigint COMMENT '时效签收延误终端入库(其他)' ,
24_aging_sign_over_time_proxy bigint COMMENT '时效签收延误代理点收入' ,
24_aging_sign_over_time_difficult bigint COMMENT '时效签收延误问题件' ,
24_aging_sign_over_time_other bigint COMMENT '时效签收延误其他' ,
24_aging_sign_over_time_transfer bigint COMMENT '时效签收延误转邮' ,
24_aging_sign_over_time_sign_count bigint COMMENT '时效签收延误已签收汇总' ,
24_aging_sign_over_time_nosign_count bigint COMMENT '时效签收延误未签收汇总' ,
00_aging_sign_over_time_network bigint COMMENT '时效签收延误网点签收' ,
00_aging_sign_over_time_terminal_pdd bigint COMMENT '时效签收延误终端入库(桃花岛认证)' ,
00_aging_sign_over_time_terminal_other bigint COMMENT '时效签收延误终端入库(其他)' ,
00_aging_sign_over_time_proxy bigint COMMENT '时效签收延误代理点收入' ,
00_aging_sign_over_time_difficult bigint COMMENT '时效签收延误问题件' ,
00_aging_sign_over_time_other bigint COMMENT '时效签收延误其他' ,
00_aging_sign_over_time_transfer bigint COMMENT '时效签收延误转邮' ,
00_aging_sign_over_time_sign_count bigint COMMENT '时效签收延误已签收汇总' ,
00_aging_sign_over_time_nosign_count bigint COMMENT '时效签收延误未签收汇总' ,
aging_sign_24_count bigint COMMENT '时效签收24点前签收汇总' ,
actual_sign_in_time_network bigint COMMENT '实际签收准点网点签收' ,
actual_sign_in_time_terminal bigint COMMENT '实际签收准点驿站/快递柜' ,
actual_sign_in_time_proxy bigint COMMENT '实际签收准点代理点收入' ,
actual_sign_in_time_count bigint COMMENT '实际签收准点汇总' ,
actual_sign_over_time_network bigint COMMENT '实际签收延误网点签收' ,
actual_sign_over_time_terminal bigint COMMENT '实际签收延误驿站/快递柜' ,
actual_sign_over_time_proxy bigint COMMENT '实际签收延误代理点收入' ,
actual_sign_over_time_difficult bigint COMMENT '实际签收延误问题件' ,
actual_sign_over_time_other bigint COMMENT '实际签收延误其他' ,
actual_sign_over_time_sign_count bigint COMMENT '实际签收延误已签收汇总' ,
actual_sign_over_time_nosign_count bigint COMMENT '实际签收延误未签收汇总' ,
actual_sign_24_count bigint COMMENT '实际签收24点前签收汇总' ,
final_plan_sign_date date COMMENT '规划签收日期' ,
aging_sign_00_count bigint COMMENT '时效签收24点前签收汇总' ,
aging_sign_22_count bigint COMMENT '时效签收24点前签收汇总' ,
22_actual_sign_in_time_network      bigint COMMENT '22点前实际签收准点网点签收' ,
22_actual_sign_in_time_terminal     bigint COMMENT '22点前实际签收准点驿站/快递柜' ,
22_actual_sign_in_time_proxy        bigint COMMENT '22点前实际签收准点代理点收入' ,
22_actual_sign_in_time_count        bigint COMMENT '22点前实际签收准点汇总' ,
22_actual_sign_over_time_network    bigint COMMENT '22点前实际签收延误网点签收' ,
22_actual_sign_over_time_terminal   bigint COMMENT '22点前实际签收延误驿站/快递柜' ,
22_actual_sign_over_time_proxy      bigint COMMENT '22点前实际签收延误代理点收入' ,
22_actual_sign_over_time_difficult  bigint COMMENT '22点前实际签收延误问题件' ,
22_actual_sign_over_time_other      bigint COMMENT '22点前实际签收延误其他' ,
22_actual_sign_over_time_sign_count  bigint COMMENT '22点前实际签收延误已签收汇总' ,
22_actual_sign_over_time_nosign_count  bigint COMMENT '22点前实际签收延误未签收汇总' ,
24_actual_sign_in_time_network      bigint COMMENT '2224点实际签收准点网点签收' ,
24_actual_sign_in_time_terminal     bigint COMMENT '2224点实际签收准点驿站/快递柜' ,
24_actual_sign_in_time_proxy        bigint COMMENT '2224点实际签收准点代理点收入' ,
24_actual_sign_in_time_count        bigint COMMENT '2224点实际签收准点汇总' ,
24_actual_sign_over_time_network    bigint COMMENT '2224点实际签收延误网点签收' ,
24_actual_sign_over_time_terminal   bigint COMMENT '2224点实际签收延误驿站/快递柜' ,
24_actual_sign_over_time_proxy      bigint COMMENT '2224点实际签收延误代理点收入' ,
24_actual_sign_over_time_difficult  bigint COMMENT '2224点实际签收延误问题件' ,
24_actual_sign_over_time_other      bigint COMMENT '2224点实际签收延误其他' ,
24_actual_sign_over_time_sign_count bigint COMMENT '2224点实际签收延误已签收汇总' ,
24_actual_sign_over_time_nosign_count bigint COMMENT '2224实际签收延误未签收汇总' ,
00_actual_sign_in_time_network      bigint COMMENT '超24点实际签收准点网点签收' ,
00_actual_sign_in_time_terminal     bigint COMMENT '超24点实际签收准点驿站/快递柜' ,
00_actual_sign_in_time_proxy        bigint COMMENT '超24点实际签收准点代理点收入' ,
00_actual_sign_in_time_count        bigint COMMENT '超24点实际签收准点汇总' ,
00_actual_sign_over_time_network    bigint COMMENT '超24点实际签收延误网点签收' ,
00_actual_sign_over_time_terminal   bigint COMMENT '超24点实际签收延误驿站/快递柜' ,
00_actual_sign_over_time_proxy      bigint COMMENT '超24点实际签收延误代理点收入' ,
00_actual_sign_over_time_difficult  bigint COMMENT '超24点实际签收延误问题件' ,
00_actual_sign_over_time_other      bigint COMMENT '超24点实际签收延误其他' ,
00_actual_sign_over_time_sign_count bigint COMMENT '超24点实际签收延误已签收汇总' ,
00_actual_sign_over_time_nosign_count bigint COMMENT '超24点实际签收延误未签收汇总' ,
actual_sign_22_count    bigint COMMENT '22点签收量' ,
actual_sign_00_count    bigint COMMENT '24点前前签收量' )
ENGINE=OLAP
DUPLICATE KEY(end_city_id,pick_city_id,end_agent_code)
comment '末端城市到城市的签收'
PARTITION BY RANGE (final_plan_sign_date) (
   START ("2023-06-14") END ("2023-07-13") EVERY (INTERVAL 1 day))
DISTRIBUTED BY HASH(end_city_id) BUCKETS 5
PROPERTIES (
"replication_num" = "3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-365",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "5",
"in_memory" = "false",
"storage_format" = "V2"
);